package com.vegetable.modules.service.finance;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.vegetable.modules.entity.finance.BankFlowImportConfig;
import com.vegetable.modules.entity.finance.Transactions;
import com.vegetable.modules.entity.finance.dto.FieldMappingDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Slf4j
public class CustomBankFlowImportService {
    
    @Resource
    private IBankFlowImportConfigService bankFlowImportConfigService;
    
    /**
     * 根据自定义配置解析银行流水文件
     * @param file 上传的文件
     * @param bankType 银行类型
     * @return 解析后的银行流水列表
     */
    public List<Transactions> parseBankFlowFile(MultipartFile file, String bankType) throws Exception {
        // 获取配置
        BankFlowImportConfig config = bankFlowImportConfigService.getByBankType(bankType);
        if (config == null) {
            throw new RuntimeException("未找到银行类型为 " + bankType + " 的导入配置");
        }
        
        // 解析字段映射配置
        JSONObject fieldMappingConfig = parseFieldMappingConfig(config.getFieldMapping());
        List<FieldMappingDTO> fieldMappings = parseFieldMapping(fieldMappingConfig);
        if (fieldMappings == null || fieldMappings.isEmpty()) {
            throw new RuntimeException("字段映射配置为空或格式不正确");
        }
        
        // 获取表头行索引
        int headerRowIndex = fieldMappingConfig.getIntValue("headerRowIndex");
        
        // 解析文件
        return parseExcelFile(file, fieldMappings, headerRowIndex);
    }
    
    /**
     * 解析字段映射配置（完整配置）
     * @param fieldMapping 字段映射配置JSON字符串
     * @return 字段映射配置JSONObject
     */
    private JSONObject parseFieldMappingConfig(String fieldMapping) {
        if (StringUtils.isBlank(fieldMapping)) {
            return new JSONObject();
        }
        
        try {
            return JSON.parseObject(fieldMapping);
        } catch (Exception e) {
            log.error("解析字段映射配置失败", e);
            return new JSONObject();
        }
    }
    
    /**
     * 解析字段映射配置
     * @param fieldMappingConfig 字段映射配置JSONObject
     * @return 字段映射DTO列表
     */
    private List<FieldMappingDTO> parseFieldMapping(JSONObject fieldMappingConfig) {
        if (fieldMappingConfig == null) {
            return Collections.emptyList();
        }
        
        try {
            // 获取mappings数组
            JSONArray array = fieldMappingConfig.getJSONArray("mappings");
            if (array != null) {
                return array.toJavaList(FieldMappingDTO.class);
            }
            
            return Collections.emptyList();
        } catch (Exception e) {
            log.error("解析字段映射配置失败", e);
            return Collections.emptyList();
        }
    }
    
    /**
     * 解析Excel文件
     * @param file Excel文件
     * @param fieldMappings 字段映射配置
     * @return 银行流水列表
     */
    private List<Transactions> parseExcelFile(MultipartFile file, List<FieldMappingDTO> fieldMappings) throws Exception {
        return parseExcelFile(file, fieldMappings, 0); // 默认从第0行开始读取表头
    }
    
    /**
     * 解析Excel文件
     * @param file Excel文件
     * @param fieldMappings 字段映射配置
     * @param headerRowIndex 表头行索引
     * @return 银行流水列表
     */
    private List<Transactions> parseExcelFile(MultipartFile file, List<FieldMappingDTO> fieldMappings, int headerRowIndex) throws Exception {
        List<Transactions> transactionsList = new ArrayList<>();
        
        // 验证字段映射配置
        for (FieldMappingDTO mapping : fieldMappings) {
            if (!mapping.isValidFieldType()) {
                log.warn("无效的字段类型: {}", mapping.getFieldType());
            }
        }
        
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            
            // 创建字段映射关系，便于后续处理
            Map<String, FieldMappingDTO> fieldMap = new HashMap<>();
            // 创建按字段类型分组的映射关系
            Map<String, List<FieldMappingDTO>> fieldTypeMap = new HashMap<>();
            
            for (FieldMappingDTO mapping : fieldMappings) {
                if (mapping.getColumnIndex() != null && mapping.getSystemFieldName() != null) {
                    fieldMap.put(mapping.getSystemFieldName(), mapping);
                }
                
                // 按字段类型分组
                String fieldType = mapping.getFieldType();
                if (fieldType != null) {
                    fieldTypeMap.computeIfAbsent(fieldType, k -> new ArrayList<>()).add(mapping);
                }
            }
            
            // 从表头行之后开始读取数据
            for (int i = headerRowIndex + 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                
                Transactions transactions = new Transactions();
                boolean hasData = false;
                
                // 根据字段映射配置填充数据
                for (FieldMappingDTO mapping : fieldMappings) {
                    Integer columnIndex = mapping.getColumnIndex();
                    String systemFieldName = mapping.getSystemFieldName();
                    String fieldType = mapping.getFieldType();
                    
                    if (columnIndex == null || systemFieldName == null || systemFieldName.isEmpty()) {
                        continue;
                    }
                    
                    // 跳过特殊处理的字段类型，在后续专门处理
                    if ("incomeAmount".equals(fieldType) || "expenseAmount".equals(fieldType) || 
                        "typeIdentifier".equals(fieldType) || "payerInfo".equals(fieldType) || 
                        "payeeInfo".equals(fieldType)) {
                        continue;
                    }
                    
                    Cell cell = row.getCell(columnIndex);
                    if (cell == null) {
                        continue;
                    }
                    
                    // 根据系统字段名称设置对应的属性值
                    setTransactionField(transactions, systemFieldName, cell);
                    hasData = true;
                }
                
                // 特殊处理交易类型和交易金额
                handleTradingTypeAndAmount(transactions, row, fieldMap, fieldTypeMap);
                
                // 特殊处理对方信息（根据交易类型确定是付款人还是收款人信息）
                handleCounterpartyInfo(transactions, row, fieldMap, fieldTypeMap);
                
                // 生成交易唯一ID
                if (hasData) {
                    transactions.setTradingNo(Transactions.generateUniqueFlowId(transactions));
                    transactionsList.add(transactions);
                }
            }
        }
        
        return transactionsList;
    }
    
    /**
     * 特殊处理对方信息（根据交易类型确定是付款人还是收款人信息）
     * @param transactions 银行流水对象
     * @param row Excel行
     * @param fieldMap 字段映射关系
     * @param fieldTypeMap 按字段类型分组的映射关系
     */
    private void handleCounterpartyInfo(Transactions transactions, Row row, Map<String, FieldMappingDTO> fieldMap, Map<String, List<FieldMappingDTO>> fieldTypeMap) {
        String tradingType = transactions.getTradingType();
        if (StringUtils.isBlank(tradingType)) {
            return;
        }
        
        // 根据交易类型确定使用付款人还是收款人信息
        if ("INCOME".equals(tradingType)) {
            // 收入交易，使用付款人信息
            setCounterpartyInfoFromPayer(transactions, row, fieldTypeMap);
        } else if ("EXPENSE".equals(tradingType)) {
            // 支出交易，使用收款人信息
            setCounterpartyInfoFromPayee(transactions, row, fieldTypeMap);
        }
    }
    
    /**
     * 从付款人信息设置对方信息
     * @param transactions 银行流水对象
     * @param row Excel行
     * @param fieldTypeMap 按字段类型分组的映射关系
     */
    private void setCounterpartyInfoFromPayer(Transactions transactions, Row row, Map<String, List<FieldMappingDTO>> fieldTypeMap) {
        // 查找付款人相关信息字段
        List<FieldMappingDTO> payerInfoFields = fieldTypeMap.get("payerInfo");
        if (payerInfoFields == null || payerInfoFields.isEmpty()) {
            return;
        }
        
        // 根据系统字段名称分组付款人信息字段
        Map<String, FieldMappingDTO> payerFieldMap = new HashMap<>();
        for (FieldMappingDTO field : payerInfoFields) {
            payerFieldMap.put(field.getSystemFieldName(), field);
        }
        
        FieldMappingDTO payerBankField = payerFieldMap.get("bankName"); // 付款人开户行名
        FieldMappingDTO payerAccountField = payerFieldMap.get("bankNo"); // 付款人账号
        FieldMappingDTO payerNameField = payerFieldMap.get("counterparty"); // 付款人名称
        
        if (payerBankField != null && payerBankField.getColumnIndex() != null) {
            Cell cell = row.getCell(payerBankField.getColumnIndex());
            if (cell != null) {
                String payerBank = parseStringCellValue(cell);
                if (StringUtils.isNotBlank(payerBank)) {
                    transactions.setBankName(payerBank); // 付款人开户行名 -> 对方银行名称
                }
            }
        }
        
        if (payerAccountField != null && payerAccountField.getColumnIndex() != null) {
            Cell cell = row.getCell(payerAccountField.getColumnIndex());
            if (cell != null) {
                String payerAccount = parseStringCellValue(cell);
                if (StringUtils.isNotBlank(payerAccount)) {
                    transactions.setBankNo(payerAccount); // 付款人账号 -> 对方银行卡ID
                }
            }
        }
        
        if (payerNameField != null && payerNameField.getColumnIndex() != null) {
            Cell cell = row.getCell(payerNameField.getColumnIndex());
            if (cell != null) {
                String payerName = parseStringCellValue(cell);
                if (StringUtils.isNotBlank(payerName)) {
                    transactions.setCounterparty(payerName); // 付款人名称 -> 交易对手方
                }
            }
        }
    }
    
    /**
     * 从收款人信息设置对方信息
     * @param transactions 银行流水对象
     * @param row Excel行
     * @param fieldTypeMap 按字段类型分组的映射关系
     */
    private void setCounterpartyInfoFromPayee(Transactions transactions, Row row, Map<String, List<FieldMappingDTO>> fieldTypeMap) {
        // 查找收款人相关信息字段
        List<FieldMappingDTO> payeeInfoFields = fieldTypeMap.get("payeeInfo");
        if (payeeInfoFields == null || payeeInfoFields.isEmpty()) {
            return;
        }
        
        // 根据系统字段名称分组收款人信息字段
        Map<String, FieldMappingDTO> payeeFieldMap = new HashMap<>();
        for (FieldMappingDTO field : payeeInfoFields) {
            payeeFieldMap.put(field.getSystemFieldName(), field);
        }
        
        FieldMappingDTO payeeBankField = payeeFieldMap.get("bankName"); // 收款人开户行名
        FieldMappingDTO payeeAccountField = payeeFieldMap.get("bankNo"); // 收款人账号
        FieldMappingDTO payeeNameField = payeeFieldMap.get("counterparty"); // 收款人名称
        
        if (payeeBankField != null && payeeBankField.getColumnIndex() != null) {
            Cell cell = row.getCell(payeeBankField.getColumnIndex());
            if (cell != null) {
                String payeeBank = parseStringCellValue(cell);
                if (StringUtils.isNotBlank(payeeBank)) {
                    transactions.setBankName(payeeBank); // 收款人开户行名 -> 对方银行名称
                }
            }
        }
        
        if (payeeAccountField != null && payeeAccountField.getColumnIndex() != null) {
            Cell cell = row.getCell(payeeAccountField.getColumnIndex());
            if (cell != null) {
                String payeeAccount = parseStringCellValue(cell);
                if (StringUtils.isNotBlank(payeeAccount)) {
                    transactions.setBankNo(payeeAccount); // 收款人账号 -> 对方银行卡ID
                }
            }
        }
        
        if (payeeNameField != null && payeeNameField.getColumnIndex() != null) {
            Cell cell = row.getCell(payeeNameField.getColumnIndex());
            if (cell != null) {
                String payeeName = parseStringCellValue(cell);
                if (StringUtils.isNotBlank(payeeName)) {
                    transactions.setCounterparty(payeeName); // 收款人名称 -> 交易对手方
                }
            }
        }
    }
    
    /**
     * 特殊处理交易类型和交易金额
     * @param transactions 银行流水对象
     * @param row Excel行
     * @param fieldMap 字段映射关系
     * @param fieldTypeMap 按字段类型分组的映射关系
     */
    private void handleTradingTypeAndAmount(Transactions transactions, Row row, Map<String, FieldMappingDTO> fieldMap, Map<String, List<FieldMappingDTO>> fieldTypeMap) {
        // 情况一：有支出流水和收入流水字段
        List<FieldMappingDTO> incomeFields = fieldTypeMap.get("incomeAmount"); // 收入流水
        List<FieldMappingDTO> expenseFields = fieldTypeMap.get("expenseAmount"); // 支出流水
        
        if ((incomeFields != null && !incomeFields.isEmpty()) || (expenseFields != null && !expenseFields.isEmpty())) {
            BigDecimal incomeAmount = null;
            BigDecimal expenseAmount = null;
            
            if (incomeFields != null && !incomeFields.isEmpty()) {
                FieldMappingDTO incomeField = incomeFields.get(0); // 取第一个匹配的字段
                if (incomeField.getColumnIndex() != null) {
                    Cell incomeCell = row.getCell(incomeField.getColumnIndex());
                    if (incomeCell != null) {
                        incomeAmount = parseBigDecimalCellValue(incomeCell);
                    }
                }
            }
            
            if (expenseFields != null && !expenseFields.isEmpty()) {
                FieldMappingDTO expenseField = expenseFields.get(0); // 取第一个匹配的字段
                if (expenseField.getColumnIndex() != null) {
                    Cell expenseCell = row.getCell(expenseField.getColumnIndex());
                    if (expenseCell != null) {
                        expenseAmount = parseBigDecimalCellValue(expenseCell);
                    }
                }
            }
            
            // 根据收入和支出金额确定交易类型和交易金额
            if (incomeAmount != null && incomeAmount.compareTo(BigDecimal.ZERO) > 0) {
                transactions.setTradingAmount(incomeAmount);
                transactions.setTradingType("INCOME"); // 收入
            } else if (expenseAmount != null && expenseAmount.compareTo(BigDecimal.ZERO) > 0) {
                transactions.setTradingAmount(expenseAmount);
                transactions.setTradingType("EXPENSE"); // 支出
            }
            return; // 处理完这种情况后直接返回
        }
        
        // 情况二和三：通过标识字段确定交易类型
        List<FieldMappingDTO> typeIdentifierFields = fieldTypeMap.get("typeIdentifier"); // 类型标识字段
        FieldMappingDTO amountField = fieldMap.get("tradingAmount"); // 金额字段
        
        if (typeIdentifierFields != null && !typeIdentifierFields.isEmpty() && 
            amountField != null && amountField.getColumnIndex() != null) {
            
            FieldMappingDTO typeIdentifierField = typeIdentifierFields.get(0); // 取第一个匹配的字段
            Cell typeCell = row.getCell(typeIdentifierField.getColumnIndex());
            Cell amountCell = row.getCell(amountField.getColumnIndex());
            
            if (typeCell != null && amountCell != null) {
                String typeValue = parseStringCellValue(typeCell);
                BigDecimal amount = parseBigDecimalCellValue(amountCell);
                
                if (amount != null) {
                    transactions.setTradingAmount(amount.abs()); // 金额取绝对值
                    
                    // 根据不同银行的标识确定交易类型
                    if (isIncomeType(typeValue)) {
                        transactions.setTradingType("INCOME"); // 收入
                    } else if (isExpenseType(typeValue)) {
                        transactions.setTradingType("EXPENSE"); // 支出
                    }
                }
            }
        }
    }
    
    /**
     * 判断是否为收入类型
     * @param typeValue 类型值
     * @return 是否为收入类型
     */
    private boolean isIncomeType(String typeValue) {
        if (StringUtils.isBlank(typeValue)) {
            return false;
        }
        
        // 转换为小写进行比较
        String lowerType = typeValue.toLowerCase().trim();
        
        // 情况二：借/贷标识（贷为收入）
        if ("贷".equals(typeValue) || "credit".equals(lowerType) || "cr".equals(lowerType)) {
            return true;
        }
        
        // 情况三：来账/往账标识（来账为收入）
        if ("来账".equals(typeValue) || "income".equals(lowerType) || "in".equals(lowerType)) {
            return true;
        }
        
        return false;
    }
    
    /**
     * 判断是否为支出类型
     * @param typeValue 类型值
     * @return 是否为支出类型
     */
    private boolean isExpenseType(String typeValue) {
        if (StringUtils.isBlank(typeValue)) {
            return false;
        }
        
        // 转换为小写进行比较
        String lowerType = typeValue.toLowerCase().trim();
        
        // 情况二：借/贷标识（借为支出）
        if ("借".equals(typeValue) || "debit".equals(lowerType) || "dr".equals(lowerType)) {
            return true;
        }
        
        // 情况三：来账/往账标识（往账为支出）
        if ("往账".equals(typeValue) || "expense".equals(lowerType) || "out".equals(lowerType)) {
            return true;
        }
        
        return false;
    }
    
    /**
     * 根据系统字段名称设置Transactions对象的属性值
     * @param transactions 银行流水对象
     * @param fieldName 系统字段名称
     * @param cell Excel单元格
     */
    private void setTransactionField(Transactions transactions, String fieldName, Cell cell) {
        try {
            switch (fieldName) {
                case "tradingTime":
                    transactions.setTradingTime(parseDateCellValue(cell));
                    break;
                case "tradingAmount":
                    // tradingAmount由handleTradingTypeAndAmount方法特殊处理
                    break;
                case "balanceAmount":
                    transactions.setBalanceAmount(parseBigDecimalCellValue(cell));
                    break;
                case "tradingType":
                    // tradingType由handleTradingTypeAndAmount方法特殊处理
                    break;
                case "status":
                    transactions.setStatus(parseStringCellValue(cell));
                    break;
                case "channel":
                    transactions.setChannel(parseStringCellValue(cell));
                    break;
                case "bankName":
                    // bankName由handleCounterpartyInfo方法特殊处理
                    break;
                case "bankNo":
                    // bankNo由handleCounterpartyInfo方法特殊处理
                    break;
                case "counterparty":
                    // counterparty由handleCounterpartyInfo方法特殊处理
                    break;
                case "tradingDescription":
                    transactions.setTradingDescription(parseStringCellValue(cell));
                    break;
                case "remark":
                    transactions.setRemark(parseStringCellValue(cell));
                    break;
                default:
                    // 其他字段暂不处理
                    break;
            }
        } catch (Exception e) {
            log.warn("设置字段值失败: fieldName={}, cellValue={}", fieldName, cell.toString(), e);
        }
    }
    
    /**
     * 解析单元格日期值
     * @param cell 单元格
     * @return 日期对象
     */
    private Date parseDateCellValue(Cell cell) {
        try {
            if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                String value = cell.getStringCellValue();
                if (StringUtils.isNotBlank(value)) {
                    // 尝试多种日期格式解析
                    String[] patterns = {
                        "yyyy-MM-dd HH:mm:ss",
                        "yyyy-MM-dd HH:mm",
                        "yyyy-MM-dd",
                        "yyyy/MM/dd HH:mm:ss",
                        "yyyy/MM/dd HH:mm",
                        "yyyy/MM/dd",
                        "yyyyMMdd"
                    };
                    
                    for (String pattern : patterns) {
                        try {
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            return sdf.parse(value);
                        } catch (Exception e) {
                            // 继续尝试下一种格式
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.warn("解析日期值失败: cellValue={}", cell.toString(), e);
        }
        return null;
    }
    
    /**
     * 解析单元格数值
     * @param cell 单元格
     * @return BigDecimal数值
     */
    private BigDecimal parseBigDecimalCellValue(Cell cell) {
        if (cell.getCellType() == CellType.NUMERIC) {
            return BigDecimal.valueOf(cell.getNumericCellValue());
        } else {
            String value = cell.getStringCellValue();
            if (StringUtils.isNotBlank(value)) {
                // 移除可能的千分位分隔符
                value = value.replace(",", "");
                // 移除货币符号
                value = value.replaceAll("[¥$€£]", "");
                return new BigDecimal(value);
            }
        }
        return null;
    }
    
    /**
     * 解析单元格字符串值
     * @param cell 单元格
     * @return 字符串值
     */
    private String parseStringCellValue(Cell cell) {
        try {
            if (cell.getCellType() == CellType.STRING) {
                return cell.getStringCellValue();
            } else if (cell.getCellType() == CellType.NUMERIC) {
                // 对于数值类型的单元格，检查是否为日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return sdf.format(date);
                }
                return String.valueOf(cell.getNumericCellValue());
            } else if (cell.getCellType() == CellType.BOOLEAN) {
                return String.valueOf(cell.getBooleanCellValue());
            } else {
                return cell.toString();
            }
        } catch (Exception e) {
            log.warn("解析字符串值失败: cellValue={}", cell.toString(), e);
            return null;
        }
    }
}